package cj.web.tel.dao;
/*
 *  
 *  
*/
import java.util.List;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;
import cj.web.tel.domain.GroupPeople;

/**
 * 
 * @author 
 *
 */
@DAO
public interface GroupPeopleDAO{
	
	@SQL("SELECT a.*,b.name as depName,c.name as groupName,d.name as officeName FROM GroupPeople a left join GroupDep b on a.depId=b.id left join GroupInfor c on a.groupId=c.id left join GroupOffices d on a.officeId=d.id WHERE a.deleteFlag=1 #if(:t.groupId != -1){ AND a.groupId=:t.groupId } #if(:t.officeId != -1){ AND a.officeId=:t.officeId } #if(:t.depId != -1){ AND a.depId=:t.depId } #if(:t.name != null){ AND a.name LIKE '%##(:t.name)%' } #if(:t.nameOrderNo == 1){ ORDER BY c.orderNo,b.orderNo,d.orderNo,a.bhSort,a.name ASC } #if(:t.nameOrderNo == 0){ ORDER BY c.orderNo,b.orderNo,d.orderNo,a.pySort,a.name ASC } ")
    List<GroupPeople> queryGl(@SQLParam("t") GroupPeople groupPeople);
		
	@SQL("SELECT count(1) FROM GroupPeople WHERE deleteFlag=1 #if(:t.groupId != -1){ AND groupId=:t.groupId } #if(:t.depId != -1){ AND depId=:t.depId } #if(:t.officeId != -1){ AND officeId=:t.officeId } #if(:t.name != null){ AND name LIKE '%##(:t.name)%' }" )
    int queryGlSize(@SQLParam("t") GroupPeople groupPeople);
	
	@SQL("SELECT a.*,b.name as depName,c.name as groupName,d.name as officeName FROM GroupPeople a left join GroupDep b on a.depId=b.id left join GroupInfor c on a.groupId=c.id left join GroupOffices d on a.officeId=d.id WHERE a.deleteFlag=1 #if(:t.groupId != null){ AND a.groupId=:t.groupId } #if(:t.officeId != null){ AND a.officeId=:t.officeId } #if(:t.depId != null){ AND a.depId=:t.depId } #if(:t.name != null){ AND a.name LIKE '%##(:t.name)%' } #if(:t.nameOrderNo == 1){ ORDER BY c.orderNo,b.orderNo,d.orderNo,a.bhSort ASC } #if(:t.nameOrderNo == 0){ ORDER BY c.orderNo,b.orderNo,d.orderNo,a.pySort ASC } LIMIT :t.start,:t.length ")
    List<GroupPeople> queryPeopleGl(@SQLParam("t") GroupPeople groupPeople);
		
	@SQL("SELECT count(1) FROM GroupPeople WHERE deleteFlag=1 #if(:t.groupId != null){ AND groupId=:t.groupId } #if(:t.depId != null){ AND depId=:t.depId } #if(:t.officeId != null){ AND officeId=:t.officeId } #if(:t.name != null){ AND name LIKE '%##(:t.name)%' }" )
    int queryPeopleGlSize(@SQLParam("t") GroupPeople groupPeople);
	
	@SQL("SELECT a.*,b.name as depName,c.name as groupName,d.name as officeName FROM GroupPeople a left join GroupDep b on a.depId=b.id left join GroupInfor c on a.groupId=c.id left join GroupOffices d on a.officeId=d.id WHERE a.deleteFlag=1 AND a.depId=:t.depId #if(:t.name != null){ AND a.name LIKE '%##(:t.name)%' } ORDER BY a.updateAt DESC LIMIT :t.start,:t.length ")
    List<GroupPeople> query(@SQLParam("t") GroupPeople groupPeople);
		
	@SQL("SELECT count(1) FROM GroupPeople WHERE deleteFlag=1 AND depId=:t.depId #if(:t.name != null){ AND name LIKE '%##(:t.name)%' }" )
    int querySize(@SQLParam("t") GroupPeople groupPeople);
    
    @SQL("SELECT a.*,b.name as depName,c.name as groupName FROM GroupPeople a join GroupDep b on a.depId=b.id join GroupInfor c on a.groupId=c.id WHERE a.id=:1 ")
    GroupPeople queryById(int id);
    
    @SQL("INSERT INTO GroupPeople (groupId,officeId,depId,type,name,descs,photo,location,netAccount,phone,officeTel,houseTel,email,orderNo,deleteFlag,createUser,createAt,updateUser,updateAt,bhSort,pySort) VALUES (:t.groupId,:t.officeId,:t.depId,:t.type,:t.name,:t.descs,:t.photo,:t.location,:t.netAccount,:t.phone,:t.officeTel,:t.houseTel,:t.email,:t.orderNo,1,:t.createUser,now(),:t.updateUser,now(),:t.bhSort,:t.pySort)")
    void create(@SQLParam("t") GroupPeople groupPeople);
	
	@SQL("UPDATE GroupPeople SET "
	    			        + "groupId=:t.groupId,"
	    			        + "officeId=:t.officeId,"
	    			        + "depId=:t.depId,"
	    			        + "type=:t.type,"
	    			        + "name=:t.name,"
	    			        + "descs=:t.descs,"
	    			        + "photo=:t.photo,"
	    			        + "location=:t.location,"
	    			        + "netAccount=:t.netAccount,"
	    			        + "phone=:t.phone,"
	    			        + "officeTel=:t.officeTel,"
	    			        + "houseTel=:t.houseTel,"
	    			        + "bhSort=:t.bhSort,"
	    			        + "pySort=:t.pySort,"
	    			        + "email=:t.email,"
	    			        + "orderNo=:t.orderNo"
    						+ " WHERE id=:t.id")
    void update(@SQLParam("t") GroupPeople groupPeople);
	
	@SQL("UPDATE GroupPeople SET groupId=:1,officeId=:2,depId=:3,location=:4 WHERE id=:5")
	void update(int groupId,int officeId,int depId,String location,int id);
    
    @SQL("UPDATE GroupPeople SET deleteFlag=0 WHERE id=:1 ")
    void deleteById(int id);
}